import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

import javax.swing.*;

class mysqlDBRealize3{
    public static Connection getMysqlConnection(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb2024110502?serverTimezone=GMT%2B8&useSSL=false","root","root");
            return conn;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();

        }
        return null;
    }
    public static Statement getMysqlStatement(Connection con) {
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            return stmt;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
public class Example1001 extends JFrame{
    public static void display1001GUI(){
        JFrame jFrame1 = new JFrame("Yiran学生成绩管理系统-成绩的增删改查");
        jFrame1.setSize(670,310);
        jFrame1.setLocation(140,80);
        jFrame1.setBackground(Color.CYAN);
        jFrame1.setLayout(new FlowLayout(FlowLayout.CENTER));
        jFrame1.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        JPanel jp1 = new JPanel();
        JPanel jp2 = new JPanel();
        JPanel jp3 = new JPanel();
        JPanel jp4 = new JPanel();
        JPanel jp5 = new JPanel();
        JLabel jl1 = new JLabel("学号：");
        JTextField jtf1 = new JTextField(20);
        JLabel jl2 = new JLabel("课程号：");
        JTextField jtf2 = new JTextField(20);
        JLabel jl3 = new JLabel("姓名：");
        JTextField jtf3 = new JTextField(20);
        JLabel jl4 = new JLabel("课程名称：");
        JTextField jtf4 = new JTextField(20);
        JLabel jl5 = new JLabel("平时成绩：");
        JTextField jtf5 = new JTextField(20);
        JLabel jl6 = new JLabel("期末成绩：");
        JTextField jtf6 = new JTextField(20);
        JLabel jl7 = new JLabel("总评成绩：");
        JTextField jtf7 = new JTextField(20);
        JButton jb1 = new JButton("新增数据");
        JButton jb2 = new JButton("查询数据");
        JButton jb3 = new JButton("修改数据");
        JButton jb4 = new JButton("删除数据");
        JButton jb5 = new JButton("退出");
        String st1 = jtf2.getText();
        String st2 = jtf2.getText();
        String st3 = jtf3.getText();
        String st4 = jtf4.getText();
        String st5 = jtf5.getText();
        String st6 = jtf6.getText();
        String st7 = jtf7.getText();
        jb1.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    Connection con1 = mysqlDBRealize3.getMysqlConnection();
                    Statement sta1 = mysqlDBRealize3.getMysqlStatement(con1);
                    int t = sta1.executeUpdate("INSERT INTO gradeinfo VALUES ('s1','s2','s3','s4','s5','s6','s7')".replace("s1",jtf1.getText()).replace("s2",jtf2.getText()).replace("s3",jtf3.getText()).replace("s4",jtf4.getText()).replace("s5",jtf5.getText()).replace("s6",jtf6.getText()).replace("s7",jtf7.getText()));
                    if(t>0){
                        JOptionPane.showMessageDialog(null,"新增成功！");
                    }else{
                        JOptionPane.showMessageDialog(null,"新增失败！");
                    }
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        jb2.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                Connection con2 = mysqlDBRealize3.getMysqlConnection();
                Statement sta2 = mysqlDBRealize3.getMysqlStatement(con2);
                try {
                    ResultSet rs1 = sta2.executeQuery("SELECT * FROM gradeinfo WHERE stuname='quyue' AND couname='kec'".replace("quyue",jtf3.getText()).replace("kec",jtf4.getText()));
                    if(rs1.next()){
                        String le1 = rs1.getString("stuid");
                        String le2 = rs1.getString("couid");
                        String le3 = rs1.getString("stuname");
                        String le4 = rs1.getString("couname");
                        String le5 = rs1.getString("score1");
                        String le6 = rs1.getString("score2");
                        String le7 = rs1.getString("score3");
                        JOptionPane.showMessageDialog(null,"查询学生成绩数据（学号："+le1+"，课程号："+le2+"，姓名："+le3+"，课程名称："+le4+"，平时成绩："+le5+"，期末成绩："+le6+"，总评成绩："+le7+"）成功！");
                    }else{
                        JOptionPane.showMessageDialog(null,"无此学生数据！");
                    }
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        jb3.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                Connection con3 = mysqlDBRealize3.getMysqlConnection();
                Statement sta3 = mysqlDBRealize3.getMysqlStatement(con3);
                try {
                    ResultSet rs1 = sta3.executeQuery("SELECT * FROM gradeinfo WHERE stuname='quyue' AND couname='kec'".replace("quyue",jtf3.getText()).replace("kec",jtf4.getText()));
                    if(rs1.next()){
                        int t = sta3.executeUpdate("UPDATE gradeinfo SET stuid='s1',couid='s2',stuname='s3',couname='s4',score1='s5',score2='s6',score3='s7' WHERE stuname='quyue' AND couname='kec'".replace("s1",jtf1.getText()).replace("s2",jtf2.getText()).replace("s3",jtf3.getText()).replace("s4",jtf4.getText()).replace("s5",jtf5.getText()).replace("s6",jtf6.getText()).replace("s7",jtf7.getText()).replace("quyue",jtf3.getText()).replace("kec",jtf4.getText()));
                        if(t>0){
                            JOptionPane.showMessageDialog(null,"修改成功！");
                        }else{
                            JOptionPane.showMessageDialog(null,"修改失败！");
                        }
                    }else{
                        JOptionPane.showMessageDialog(null,"修改失败！");
                    }
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        jb4.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                Connection con4 = mysqlDBRealize3.getMysqlConnection();
                Statement sta4 = mysqlDBRealize3.getMysqlStatement(con4);
                try {
                    int t0 = sta4.executeUpdate("DELETE FROM gradeinfo WHERE stuname='qqq' AND couname='rrr'".replace("qqq",jtf3.getText()).replace("rrr",jtf4.getText()));
                    if(t0>0){
                        JOptionPane.showMessageDialog(null,"删除成功！");
                    }else{
                        JOptionPane.showMessageDialog(null,"删除失败！");
                    }
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        jb5.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                System.exit(0);
            }
        });
        jp1.add(jl1);
        jp1.add(jtf1);
        jp1.add(jl2);
        jp1.add(jtf2);
        jp2.add(jl3);
        jp2.add(jtf3);
        jp2.add(jl4);
        jp2.add(jtf4);
        jp3.add(jl5);
        jp3.add(jtf5);
        jp3.add(jl6);
        jp3.add(jtf6);
        jp4.add(jl7);
        jp4.add(jtf7);
        jp5.add(jb1);
        jp5.add(jb2);
        jp5.add(jb3);
        jp5.add(jb4);
        jp5.add(jb5);
        jFrame1.add(jp1);
        jFrame1.add(jp2);
        jFrame1.add(jp3);
        jFrame1.add(jp4);
        jFrame1.add(jp5);
        jFrame1.setVisible(true);
    }
    public static void main(String[] args) {
        Example1001.display1001GUI();
    }
}